package creditsProject.server.dao.impl;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import creditsProject.dto.User;
import creditsProject.server.dao.ManagerDao;

public class ManagerDaoImpl implements ManagerDao {

	final String derbyProtocol = "jdbc:derby://localhost:1527/";
	final String dbName = "BU";
	final String jdbcURL = derbyProtocol + dbName;
	final String derbyDriver = "org.apache.derby.jdbc.ClientDriver";

	private Map<Integer, User> inMemoryUsers = new HashMap<Integer, User>();

	@Override
	public List<User> loadAllClients() {
		List<User> res = new ArrayList<User>();

		Connection conn = null;
		try {
			System.setProperty("jdbc.drivers", derbyDriver);
			conn = DriverManager.getConnection(jdbcURL, "admin", "admin");

			PreparedStatement ps = conn
					.prepareStatement("select MANAGER_ID, FIO, PHONE_NUMBER, ADDRESS, DATE_OF_BIRTH, PASS_HASH, CREDITS from APP.MANAGERS");
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				User user = new User();
				user.setId(rs.getInt("MANAGER_ID"));
				user.setFio(rs.getString("FIO"));
				user.setAddress(rs.getString("ADDRESS"));
				user.setDateOfBirth(rs.getString("DATE_OF_BIRTH"));
				user.setPasswordHash(rs.getString("PASS_HASH"));
				user.setPhoneNumber(rs.getInt("PHONE_NUMBER"));
				user.setCredits(rs.getString("CREDITS"));
				res.add(user);
			}

			return res;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return res;

	}

	@Override
	public void addClient(User user) {
		Connection conn = null;
		System.setProperty("jdbc.drivers", derbyDriver);
		try {
			conn = DriverManager.getConnection(jdbcURL, "admin", "admin");
			PreparedStatement ps = conn
					.prepareStatement("select USER_ID from app.users ORDER BY USER_ID DESC");
			ResultSet rs = ps.executeQuery();
			List<Integer> previuosIds = new ArrayList<>();
			while(rs.next()){
				previuosIds.add(rs.getInt("USER_ID"));
				}
				Integer previuosId = previuosIds.get(0);
			ps = conn
					.prepareStatement("insert into app.users (USER_ID, FIO, PHONE_NUMBER, ADDRESS, DATE_OF_BIRTH, PASS_HASH, CREDITS) values (?,?,?,?,?,?,?)");
			ps.setInt(1, previuosId + 1);
			ps.setString(2, user.getFio());
			ps.setInt(3, user.getPhoneNumber());
			ps.setString(4, user.getAddress());
			ps.setString(5, user.getDateOfBirth());
			ps.setString(6, user.getPasswordHash());
			ps.setString(7, user.getCredits());
			ps.executeUpdate();
			System.out.println("User created" + user.getFio());

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	@Override
	public User getUserByPhone(int phone) {
		Connection conn = null;
		try {
			System.setProperty("jdbc.drivers", derbyDriver);
			conn = DriverManager.getConnection(jdbcURL, "admin", "admin");

			PreparedStatement ps = conn
					.prepareStatement("select USER_ID, FIO, PHONE_NUMBER, ADDRESS, DATE_OF_BIRTH, PASS_HASH, CREDITS from APP.users WHERE PHONE_NUMBER=?");
			ps.setInt(1, phone);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				User user = new User();
				user.setId(rs.getInt("USER_ID"));
				user.setFio(rs.getString("FIO"));
				user.setAddress(rs.getString("ADDRESS"));
				user.setDateOfBirth(rs.getString("DATE_OF_BIRTH"));
				user.setPasswordHash(rs.getString("PASS_HASH"));
				user.setPhoneNumber(rs.getInt("PHONE_NUMBER"));
				user.setCredits(rs.getString("CREDITS"));
				//System.out.println("User loaded " + user.getFio());
				return user;
			}

			return null;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;

	}

	@Override
	public User getManagerByPhone(int phone) {
		Connection conn = null;
		try {
			System.setProperty("jdbc.drivers", derbyDriver);
			conn = DriverManager.getConnection(jdbcURL, "admin", "admin");

			PreparedStatement ps = conn
					.prepareStatement("select MANAGER_ID, FIO, PHONE_NUMBER, ADDRESS, DATE_OF_BIRTH, PASS_HASH, CREDITS from APP.MANAGERS WHERE PHONE_NUMBER=?");
			ps.setInt(1, phone);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				User user = new User();
				user.setId(rs.getInt("MANAGER_ID"));
				user.setFio(rs.getString("FIO"));
				user.setAddress(rs.getString("ADDRESS"));
				user.setDateOfBirth(rs.getString("DATE_OF_BIRTH"));
				user.setPasswordHash(rs.getString("PASS_HASH"));
				user.setPhoneNumber(rs.getInt("PHONE_NUMBER"));
				user.setCredits(rs.getString("CREDITS"));
				System.out.println("User loaded " + user.getFio());
				return user;
			}

			return null;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;

	}

}
